package com.cennavi.bigdata.baotou.dao.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cennavi.bigdata.baotou.dao.CrontabDao;
import com.cennavi.bigdata.baotou.util.PropertiesUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by cennavi on 2018/4/11.
 */
@Repository
public class CrontabDaoImpl implements CrontabDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private JdbcTemplate jdbcTemplatePG;

    @Override
    public List<JSONObject> getRoadIdById() {
        SqlRowSet resultSet = null;
        Set<String> strings = new HashSet<>();
        List<String> list = new ArrayList<>();
        String  sql = "SELECT s.id,r.roadid,s.roadlength FROM " +
                "road_piecewise_data s ,road_piecewise_data_new_t r,swan_road_piecewise_middle p " +
                "WHERE  r.roadid=p.roadid and s.id=p.id ";
        JSONObject roadJson = new JSONObject();
        resultSet = jdbcTemplate.queryForRowSet(sql);

        while (resultSet.next()){
            JSONObject roadGTJson = new JSONObject();
            String roadid = resultSet.getString("roadid");
            String id = resultSet.getString("id");
            String roadlength = resultSet.getString("roadlength");
            roadGTJson.put("id",id);
            roadGTJson.put("roadlength",roadlength);
            list.add(id);
            strings.add(roadid);
            if (roadJson.containsKey(roadid)){
                List<JSONObject> jsonObjectList = (List<JSONObject>) roadJson.get(roadid);
                jsonObjectList.add(roadGTJson);
                roadJson.put(roadid,jsonObjectList);
            }else {
                List<JSONObject> jsonObjects = new ArrayList<>();
                jsonObjects.add(roadGTJson);
                roadJson.put(roadid,jsonObjects);
            }
        }
        String RoadIndexRTUrl = PropertiesUtil.getProperties().getString("RoadIndexRT");
        String roadIndexRT = sendGet(RoadIndexRTUrl);
        JSONArray objects = JSONArray.parseArray(roadIndexRT);

        List<JSONObject> listJson = getListJson(objects, list, strings, roadJson);

        return listJson;
    }


    public List<JSONObject> getListJson(JSONArray objects ,List<String> list,Set<String> strings,JSONObject roadJson){

        JSONObject roadappjson = new JSONObject();
        for (Object object : objects) {
            JSONObject jsonObject = JSONObject.parseObject(object.toString());
            JSONObject jsonObject1 = new JSONObject();
            String id = jsonObject.getString("id");
            if (list.contains(id)){
                double avgspeed = jsonObject.getDouble("avgspeed");
                double c_index = jsonObject.getDouble("c_index");
                jsonObject1.put("avgspeed",avgspeed);
                jsonObject1.put("c_index",c_index);
                roadappjson.put(id,jsonObject1);
            }
        }
        ArrayList<JSONObject> jsonObjects = new ArrayList<>();
        Iterator<String> iterator = strings.iterator();//遍历roadid
        while (iterator.hasNext()){
            JSONObject roadjsonObject = new JSONObject();
            String roadid = iterator.next();
            List<JSONObject> jsons = (List<JSONObject>) roadJson.get(roadid);
            int count = 0;
            double travelTime = 0.0;
            double index = 0.0;
            double avg = 0.0;
            for (JSONObject json : jsons) {
                String id = (String) json.get("id");
                double roadlength = json.getDouble("roadlength");
                if (roadappjson.containsKey(id)){
                    JSONObject jsonObject = (JSONObject) roadappjson.get(id);
                    double avgspeed = (double) jsonObject.get("avgspeed");
                    double c_index = (double) jsonObject.get("c_index");
                    if (avgspeed!=0.0){
                        travelTime+=(roadlength*1000)/(avgspeed/3.6);
                        index+= c_index;
                        avg+= avgspeed;
                        count++;
                    }
                }
            }
            DecimalFormat df = new DecimalFormat("#.#");
            if (count!=0&&avg!=0){
                String avgIndex = df.format(index/count);
                String avgCarSpeed = df.format(avg/count);
                String traveltime = df.format(travelTime);
//                String format = new SimpleDateFormat("yyyy-MM-dd HH:mm:00").format(new Date());
                String format = new SimpleDateFormat("yyyyMMddHHmm").format(new Date());
                roadjsonObject.put("travelTime",traveltime);
                roadjsonObject.put("avgIndex",avgIndex);
                roadjsonObject.put("avgCarSpeed",avgCarSpeed);
                roadjsonObject.put("time",format);
                roadjsonObject.put("roadid",roadid);
//                System.out.println("travleTime:"+traveltime + " ->id"+roadid);
                jsonObjects.add(roadjsonObject);
            }
        }
        return jsonObjects;
    }

    @Override
    public boolean insertRoadIndexTravelInfo(final List<JSONObject> list) throws Exception {

        final List<JSONObject> jsonObjectList = list;
        String  sql = "INSERT INTO road_index_traveltime_info_2018 (ROADID,AVGSPEED,TRAVELTIME,time,ROADINDEX) VALUES (?,?,?,?,?)";
        int[] ints = new int[0];
        try {
             ints = jdbcTemplatePG.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement statement, int i) throws SQLException {
                    JSONObject jsonObject = jsonObjectList.get(i);
                    statement.setString(1, jsonObject.getString("roadid"));
                    statement.setFloat(2, Float.valueOf(jsonObject.getString("avgCarSpeed")));
                    statement.setFloat(3, Float.valueOf(jsonObject.getString("travelTime")));
                    statement.setLong(4, Long.valueOf(jsonObject.getString("time")));
                    statement.setFloat(5, Float.valueOf(jsonObject.getString("avgIndex")));
                }

                @Override
                public int getBatchSize() {
                    return list.size();
                }
            });
        }catch (Exception e){
            e.getStackTrace();
        }
        System.out.println("成功插入数据"+ ints.length + "条");
        if (ints.length>0){
            return true;
        }else {
            return false;
        }
    }

    @Override
    public boolean deleteHistoryInfo(String date) {

        String dateTime = date + " 00:00:00";
        String sql = "delete from ROAD_INDEX_TRAVELTIME_INFO a where a.time < ?";
        int update = jdbcTemplate.update(sql, dateTime);
        System.out.println("删除数据条数：" + update );
        if (update>0){
            return true;
        }else {
            return false;
        }
    }

    @Override
    public List<JSONObject> getInfoFromOracle(String start, String end) {

        List<JSONObject> list = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:00");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmm");
        String sql = "select t.roadid,t.avgspeed,t.traveltime,t.time,t.roadindex from ROAD_INDEX_TRAVELTIME_INFO t where t.time>=? and t.time<=?";
        SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, start, end);
        while (rowSet.next()){
            JSONObject json = new JSONObject();
            json.put("roadid",rowSet.getString(1));
            json.put("avgCarSpeed",rowSet.getString(2));
            json.put("travelTime",rowSet.getString(3));
            String time = rowSet.getString(4);
            String format = null;
            try {
                format = sdf2.format(sdf.parse(time));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            json.put("time",format);
            json.put("avgIndex",rowSet.getString(5));
            list.add(json);
        }
        return list;
    }

    @Override
    public void saveIntoRoadDayIndex(final JSONArray road) {
        String sql = "INSERT INTO road_day_index VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplatePG.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String,Object> map = (Map<String, Object>) road.get(i);
                ps.setString(1,map.get("ID").toString());
                ps.setString(2,map.get("ROADNAME").toString());
                ps.setLong(3,Long.valueOf(map.get("time").toString()));
                ps.setFloat(4,Float.valueOf(map.get("AVGSPEED").toString()));
                ps.setFloat(5,Float.valueOf(map.get("MAXSPEED").toString()));
                ps.setFloat(6,Float.valueOf(map.get("MINSPEED").toString()));
                ps.setFloat(7,Float.valueOf(map.get("AVGINDEX").toString()));
                ps.setFloat(8,Float.valueOf(map.get("MAXINDEX").toString()));
                ps.setFloat(9,Float.valueOf(map.get("MININDEX").toString()));
                ps.setInt(10,Integer.valueOf(map.get("AVGTRAVELTIME").toString()));
                ps.setInt(11,Integer.valueOf(map.get("quarterofyear").toString()));
            }

            @Override
            public int getBatchSize() {
                return road.size();
            }
        });
    }

    @Override
    public void saveIntoAreaDayIndex(final JSONArray area) {

        String sql = "INSERT INTO area_day_index VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplatePG.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String,Object> map = (Map<String, Object>) area.get(i);
                ps.setLong(1,Long.valueOf(map.get("AREA").toString()));
                ps.setString(2,map.get("TEAMNAME").toString());
                ps.setLong(3,Long.valueOf(map.get("time").toString()));
                ps.setFloat(4,Float.valueOf(map.get("AVGSPEED").toString()));
                ps.setFloat(5,Float.valueOf(map.get("MAXSPEED").toString()));
                ps.setFloat(6,Float.valueOf(map.get("MINSPEED").toString()));
                ps.setFloat(7,Float.valueOf(map.get("AVGINDEX").toString()));
                ps.setFloat(8,Float.valueOf(map.get("MAXINDEX").toString()));
                ps.setFloat(9,Float.valueOf(map.get("MININDEX").toString()));
                ps.setInt(10,Integer.valueOf(map.get("AVGTRAVELTIME").toString()));
                ps.setInt(11,Integer.valueOf(map.get("quarterofyear").toString()));
            }

            @Override
            public int getBatchSize() {
                return area.size();
            }
        });
    }

    @Override
    public void deleteDayIndexByTime(String time) {
        String sql = "delete from road_day_index where time="+time +";delete from area_day_index where time="+time+";";
        jdbcTemplatePG.execute(sql);
    }

    @Override
    public void saveIntoRoadHourIndex(final JSONArray roadmap) {
        String sql = "INSERT INTO road_area_hour_index VALUES (?,?,?,?,?,?)";
        /*Iterator<String> iterator = roadmap.keySet().iterator();
        final List<String> list = new ArrayList<>();
        while (iterator.hasNext()){
            list.add(iterator.next());
        }*/
        jdbcTemplatePG.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String,Object> map = (Map<String, Object>) roadmap.get(i);
                ps.setString(1,map.get("ID").toString());
                ps.setLong(2,Long.valueOf(map.get("time").toString()));
                ps.setFloat(4,Float.valueOf(map.get("AVGSPEED").toString()));
                ps.setFloat(3,Float.valueOf(map.get("AVGINDEX").toString()));
                ps.setInt(5,Integer.valueOf(map.get("quarterofyear").toString()));
                ps.setLong(6,Integer.valueOf(map.get("HOUR").toString()));
            }
            @Override
            public int getBatchSize() {
                return roadmap.size();
            }
        });
    }

    @Override
    public void saveIntoAreaHourIndex(final JSONArray areamap) {
        String sql = "INSERT INTO road_area_hour_index VALUES (?,?,?,?,?,?)";
        jdbcTemplatePG.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String,Object> map = (Map<String, Object>) areamap.get(i);
                ps.setString(1,map.get("AREA").toString());
                ps.setLong(2,Long.valueOf(map.get("time").toString()));
                ps.setFloat(4,Float.valueOf(map.get("AVGSPEED").toString()));
                ps.setFloat(3,Float.valueOf(map.get("AVGINDEX").toString()));
                ps.setInt(5,Integer.valueOf(map.get("quarterofyear").toString()));
                ps.setLong(6,Integer.valueOf(map.get("HOUR").toString()));
            }
            @Override
            public int getBatchSize() {
                return areamap.size();
            }
        });
    }

    @Override
    public void deleteHourIndexByTime(String time) {

        String sql = "DELETE FROM road_area_hour_index a WHERE a.\"time\"="+time.substring(0,8)+" AND  a.\"hour\"="+time.substring(8);
        jdbcTemplatePG.execute(sql);
    }

    @Override
    public int saveRoadPredictBaseWork(final List<Map<String, Object>> list, final String type) {

        String  sql = "INSERT INTO road_predict_base VALUES(?,?,?,?,?,?)";
        int[] ints = jdbcTemplatePG.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {

                Map<String, Object> map = list.get(i);
                ps.setString(1, map.get("ID").toString());
                ps.setFloat(2, Float.valueOf(map.get("AVGSPEED1").toString()));
                ps.setFloat(3, Float.valueOf(map.get("INDEX1").toString()));
                ps.setString(4, map.get("TIME_1").toString());
                ps.setInt(5, Integer.valueOf(map.get("TRAVELTIME").toString()));
                if (type.equals("work")) {
                    ps.setShort(6, (short) 0);
                } else {
                    ps.setShort(6, (short) 1);
                }
            }
            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        return ints.length;
    }

    @Override
    public void saveQuarter(JSONObject jsonObject) {

        String sql = "INSERT INTO quarterofyear VALUES (?,?)";
        String id = jsonObject.getString("id");
        String name = jsonObject.getString("name");
        int update = jdbcTemplatePG.update(sql, Integer.valueOf(id), name);
    }

    @Override
    public void deleteRoadPredictHis() {
        String sql = "TRUNCATE TABLE road_predict_base";
        jdbcTemplatePG.execute(sql);
    }

    public String sendGet(String url) {
        String result = "";
        BufferedReader in = null;
        URL realUrl = null;
        try {
            String urlNameString = url;
            realUrl = new URL(urlNameString);
            System.out.println(realUrl);
            URLConnection connection = realUrl.openConnection();
            connection.setRequestProperty("connection", "close");
            connection.connect();
            in = new BufferedReader(new InputStreamReader(
                    connection.getInputStream(),"UTF-8"));
            String line;
            while ((line = in.readLine()) != null) {
                result += line;
            }
        } catch (Exception e) {
            System.out.println("发送GET请求出现异常！" + e);
            e.printStackTrace();
            try {
                if (in != null) {
                    in.close();
                }
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        // 使用finally块来关闭输入流
        finally {
            try {
                if (in != null) {
                    in.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        System.out.println(result);
        return result;
    }
}
